package hellotabwidget.pack;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class EconomyDb {

	private static final String DATABASE_NAME = "economy_Db";
	private static final String DATABASE_TABLE = "economy_Table";
	private static final int DATABASE_VERSION = 1;

	public static final String KEY_ROWID = "_id";
	public static final String KEY_DATE = "date";
	public static final String KEY_EXPENSE = "expense";
	public static final String KEY_CATEGORY = "category";
	public static final String KEY_TYPE = "type";
	public static final String KEY_SUM = "sum";

	private DbHelper ourHelper;
	private final Context ourContext;
	private SQLiteDatabase ourDatabase;

	private static class DbHelper extends SQLiteOpenHelper {
		
		

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_DATE
					+ " VARCHAR NOT NULL, " + KEY_EXPENSE + " TEXT NOT NULL, "
					+ KEY_CATEGORY + " TEXT NOT NULL, " + KEY_TYPE
					+ " TEXT NOT NULL, " + KEY_SUM + " INTEGER NOT NULL);");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			onCreate(db);

		}
	}

	public EconomyDb(Context c) {
		ourContext = c;
	}

	public EconomyDb open() throws SQLException {
		ourHelper = new DbHelper(ourContext);
		ourDatabase = ourHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		ourHelper.close();
	}

	public long createEntry(int type, String dayDate, String expense,
			String category, int sum) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_TYPE, type);
		cv.put(KEY_DATE, dayDate);
		cv.put(KEY_EXPENSE, expense);
		cv.put(KEY_CATEGORY, category);
		cv.put(KEY_SUM, sum);
		return ourDatabase.insert(DATABASE_TABLE, null, cv);

	}

	public int addSumCloths() {
		String[] columns = new String[] { KEY_ROWID, KEY_DATE, KEY_EXPENSE,	KEY_CATEGORY, KEY_SUM };
		Cursor c = ourDatabase.query(DATABASE_TABLE,columns,KEY_CATEGORY + "=" + "'Cloths'" , null, null, null, null);

		int iSum = c.getColumnIndex(KEY_SUM);
		int sum = 0;

		for (c.moveToLast(); !c.isBeforeFirst(); c.moveToPrevious()) {
			sum = sum + c.getInt(iSum);
			}		
		return sum;
	}
	
	
	private int addSumFood() {
		String[] columns = new String[] { KEY_ROWID, KEY_DATE, KEY_EXPENSE,	KEY_CATEGORY, KEY_SUM };
		Cursor c = ourDatabase.query(DATABASE_TABLE,columns,KEY_CATEGORY + "=" + "'Food'" , null, null, null, null);

		int iSum = c.getColumnIndex(KEY_SUM);
		int sum = 0;

		for (c.moveToLast(); !c.isBeforeFirst(); c.moveToPrevious()) {
			sum = sum + c.getInt(iSum);
			}		
		return sum;
	}
	
	private int addSumTransportation() {
		String[] columns = new String[] { KEY_ROWID, KEY_DATE, KEY_EXPENSE,	KEY_CATEGORY, KEY_SUM };
		Cursor c = ourDatabase.query(DATABASE_TABLE,columns,KEY_CATEGORY + "=" + "'Transportation'" , null, null, null, null);

		int iSum = c.getColumnIndex(KEY_SUM);
		int sum = 0;

		for (c.moveToLast(); !c.isBeforeFirst(); c.moveToPrevious()) {
			sum = sum + c.getInt(iSum);
			}		
		return sum;
	}
	
	private int addSumHobby() {
		String[] columns = new String[] { KEY_ROWID, KEY_DATE, KEY_EXPENSE,	KEY_CATEGORY, KEY_SUM };
		Cursor c = ourDatabase.query(DATABASE_TABLE,columns,KEY_CATEGORY + "=" + "'Hobby'" , null, null, null, null);

		int iSum = c.getColumnIndex(KEY_SUM);
		int sum = 0;

		for (c.moveToLast(); !c.isBeforeFirst(); c.moveToPrevious()) {
			sum = sum + c.getInt(iSum);
			}		
		return sum;
	}

	public String getData() {
		// TODO Auto-generated method stub
		String[] columns = new String[] { KEY_ROWID, KEY_DATE, KEY_EXPENSE,
				KEY_CATEGORY, KEY_SUM };
		Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null,
				null, null);
		String result = "";
		int iRow = c.getColumnIndex(KEY_ROWID);
		int iDate = c.getColumnIndex(KEY_DATE);
		int iExpense = c.getColumnIndex(KEY_EXPENSE);
		int iCategory = c.getColumnIndex(KEY_CATEGORY);//!c.moveToPosition(fetchPlacesCount() - 5)
		int iSum = c.getColumnIndex(KEY_SUM);

			for (c.moveToLast(); !c.isBeforeFirst(); c.moveToPrevious()) {
				result = result + c.getString(iDate) + "       "
						+ c.getString(iExpense) + "         "
						+ c.getString(iCategory) + "        " + c.getInt(iSum)
						+ "KR" + "\n";

			}
		

		return result;
	}

}
